﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Proy200Millas_BE;

namespace Proy200Millas_ADO
{
    public class EmpleadoADO
    {
        ConexionADO MiConexion = new ConexionADO();
        SqlConnection cnx = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        Boolean vexito = false;
        SqlDataReader dtr = default(SqlDataReader);



        public bool InsertarEmpleado(EmpleadoBE _emplBE)
        {
            cnx.ConnectionString = MiConexion.GetCnx();
            cmd.Connection = cnx;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_InsertarVendedor";
            //Agregamos parametros 
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vnom_emp", SqlDbType.VarChar, 12));
                cmd.Parameters["@vnom_emp"].Value = _emplBE.Nom_emp;
                cmd.Parameters.Add(new SqlParameter("@vape_pat", SqlDbType.VarChar, 15));
                cmd.Parameters["@vape_pat"].Value = _emplBE.Ape_pat;
                cmd.Parameters.Add(new SqlParameter("@vape_mat", SqlDbType.VarChar, 15));
                cmd.Parameters["@vape_mat"].Value = _emplBE.Ape_mat;
                cmd.Parameters.Add(new SqlParameter("@vfec_nac", SqlDbType.SmallDateTime));
                cmd.Parameters["@vfec_nac"].Value = _emplBE.Fec_nac;
                cmd.Parameters.Add(new SqlParameter("@vfec_ing", SqlDbType.SmallDateTime));
                cmd.Parameters["@vfec_ing"].Value = _emplBE.Fec_ing;
                cmd.Parameters.Add(new SqlParameter("@vdni", SqlDbType.VarChar, 8));
                cmd.Parameters["@vdni"].Value = _emplBE.Dni;
                cmd.Parameters.Add(new SqlParameter("@vest_civ", SqlDbType.Char, 1));
                cmd.Parameters["@vest_civ"].Value = _emplBE.Est_civ;
                cmd.Parameters.Add(new SqlParameter("@vhab_bas", SqlDbType.Money));
                cmd.Parameters["@vhab_bas"].Value = _emplBE.Hab_bas;
                cmd.Parameters.Add(new SqlParameter("@vcod_bar", SqlDbType.VarChar, 3));
                cmd.Parameters["@vcod_bar"].Value = _emplBE.Hab_bas;
                cnx.Open();
                cmd.ExecuteNonQuery();
                vexito = true;
            }
            catch (SqlException x)
            {
                vexito = false;
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                cmd.Parameters.Clear();
            }
            return vexito;
        }

        public bool ActualizarEmpleado(EmpleadoBE _emplBE)
        {
            cnx.ConnectionString = MiConexion.GetCnx();
            cmd.Connection = cnx;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_ActualizarEmpleado";
            //Agregamos parametros 
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vcod_emp", SqlDbType.VarChar, 3));
                cmd.Parameters["@vcod_emp"].Value = _emplBE.Cod_emp;
                cmd.Parameters.Add(new SqlParameter("@vnom_emp", SqlDbType.VarChar, 12));
                cmd.Parameters["@vnom_emp"].Value = _emplBE.Nom_emp;
                cmd.Parameters.Add(new SqlParameter("@vape_pat", SqlDbType.VarChar, 15));
                cmd.Parameters["@vape_pat"].Value = _emplBE.Ape_pat;
                cmd.Parameters.Add(new SqlParameter("@vape_mat", SqlDbType.VarChar, 15));
                cmd.Parameters["@vape_mat"].Value = _emplBE.Ape_mat;
                cmd.Parameters.Add(new SqlParameter("@vfec_nac", SqlDbType.SmallDateTime));
                cmd.Parameters["@vfec_nac"].Value = _emplBE.Fec_nac;
                cmd.Parameters.Add(new SqlParameter("@vfec_ing", SqlDbType.SmallDateTime));
                cmd.Parameters["@vfec_ing"].Value = _emplBE.Fec_ing;
                cmd.Parameters.Add(new SqlParameter("@vdni", SqlDbType.VarChar, 8));
                cmd.Parameters["@vdni"].Value = _emplBE.Dni;
                cmd.Parameters.Add(new SqlParameter("@vest_civ", SqlDbType.Char, 1));
                cmd.Parameters["@vest_civ"].Value = _emplBE.Est_civ;
                cmd.Parameters.Add(new SqlParameter("@vhab_bas", SqlDbType.Money));
                cmd.Parameters["@vhab_bas"].Value = _emplBE.Hab_bas;
                cmd.Parameters.Add(new SqlParameter("@vcod_bar", SqlDbType.VarChar, 3));
                cmd.Parameters["@vcod_bar"].Value = _emplBE.Hab_bas;
                cnx.Open();
                cmd.ExecuteNonQuery();
                vexito = true;
            }
            catch (SqlException x)
            {
                vexito = false;
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                cmd.Parameters.Clear();
            }
            return vexito;
        }

        public bool EliminarEmpleado(String vcod)
        {
            cnx.ConnectionString = MiConexion.GetCnx();
            cmd.Connection = cnx;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_EliminarEmpleado";
            //Agregamos parametros 
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vcod_emp", SqlDbType.VarChar, 3));
                cmd.Parameters["@vcod_emp"].Value = vcod;
                cnx.Open();
                cmd.ExecuteNonQuery();
                vexito = true;
            }
            catch (SqlException x)
            {
                vexito = false;
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                // No olvidarse!!!!!!
                cmd.Parameters.Clear();
            }
            return vexito;
        }

        public EmpleadoBE ConsultarEmpleado(string vcod)
        {
            EmpleadoBE _vendBE = new EmpleadoBE();
            try
            {
                cnx.ConnectionString = MiConexion.GetCnx();
                cmd.Connection = cnx;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_ConsultarEmpleado";
                //Agregamos el parametro para el SP
                cmd.Parameters.Add(new SqlParameter("@vcod_emp", SqlDbType.VarChar, 3));
                cmd.Parameters["@vcod_emp"].Value = vcod;
                cnx.Open();
                dtr = cmd.ExecuteReader();
                if (dtr.HasRows == true)
                {
                    dtr.Read();
                    var _mivend = _vendBE;
                    _mivend.Cod_emp = dtr.GetValue(dtr.GetOrdinal("cod_emp")).ToString();
                    _mivend.Nom_emp = dtr.GetValue(dtr.GetOrdinal("nom_emp")).ToString();
                    _mivend.Ape_pat = dtr.GetValue(dtr.GetOrdinal("ape_pat")).ToString();
                    _mivend.Ape_mat = dtr.GetValue(dtr.GetOrdinal("ape_mat")).ToString();
                    _mivend.Fec_nac = Convert.ToDateTime(dtr.GetValue(dtr.GetOrdinal("fec_nac")));
                    _mivend.Fec_ing = Convert.ToDateTime(dtr.GetValue(dtr.GetOrdinal("fec_ing")));
                    _mivend.Dni = dtr.GetValue(dtr.GetOrdinal("dni")).ToString();
                    _mivend.Est_civ = dtr.GetValue(dtr.GetOrdinal("est_civ")).ToString();
                    _mivend.Hab_bas = Convert.ToSingle(dtr.GetValue(dtr.GetOrdinal("hab_bas")));
                    _mivend.Cod_barFK = dtr.GetValue(dtr.GetOrdinal("cod_bar")).ToString();
                    dtr.Close();
                }
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                cmd.Parameters.Clear();
            }
            return _vendBE;
        }
        public DataTable ListarEmpleado()
        {
            DataSet dts = new DataSet();
            try
            {
                cnx.ConnectionString = MiConexion.GetCnx();
                cmd.Connection = cnx;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_ListarEmpleado";
                SqlDataAdapter miada = default(SqlDataAdapter);
                miada = new SqlDataAdapter(cmd);
                miada.Fill(dts, "Empleado");
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return dts.Tables["Empleado"];
        }

    }
}
